package com.pcxt.database;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.*;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import java.util.*;

import com.pcxt.exception.*;

public class DatabaseProcessor extends SQLiteOpenHelper{

	/*
	 * private static variables
	 */
	private final static int DB_VERSION = 1;
	private final static String DB_NAME = "XFamily.db";
	private final static String createTableScript =
		"Create Table Servers (" +
		"					  ServerName TEXT," +
		"					  ServerURL TEXT," +
		"					  UserId TEXT," +
		"					  Password TEXT, " +
		"					  Primary Key (ServerName ASC)" +
		"					  )";		
	private final static String createTableScript1 = 
		"Create Table ItemHistory (" +
		"					  obid TEXT," +
		"					  workItemName TEXT," +
		"					  submittedTime TEXT," +
		"					  comment TEXT," +
		"					  route TEXT," +
		"					  instructions TEXT," +
		"					  role TEXT," +
		"					  ServerName TEXT," +
		"					  ServerURL TEXT," +
		"					  Primary key (obid ASC))";		
	
	
	/*
	 * public update functions
	 */
	public void update(String statement) throws XException {
		//SQLiteDatabase db = this.getReadableDatabase();
		executeUpdate(statement);
	}
	
	public void delete(String statement) throws XException {
		//SQLiteDatabase db = this.getReadableDatabase();
		executeUpdate(statement);
	}	
	
	public void insert(String statement) throws XException {
		//SQLiteDatabase db = this.getReadableDatabase();
		executeUpdate(statement);
	}
	
	
	public void executeUpdate(String statement) throws XException{
		SQLiteDatabase db = this.getReadableDatabase();
		try {
			db.execSQL(statement);
		}
		catch (SQLException se) {
			throw new XException(se.getMessage());
		}
	}
	
	/*
	 * public query functions
	 */

	public Vector<Vector<String>> queryHistories() {
		Vector<Vector<String>> result = new Vector<Vector<String>>();
		Vector<String> server = null;
		
		String sqlStatement = "select workItemName,submittedTime,obid from ItemHistory " ;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(sqlStatement, null);
		
		for (int i = 0; i < cursor.getCount(); i ++)  {
			server = new Vector<String>();
			
			server.add(cursor.getString(0)); // workItemName
			server.add(cursor.getString(1)); // submittedTime
			server.add(cursor.getString(2)); // obid
			
			result.add(server);
			cursor.moveToNext();
		}
		cursor.close();
		return result;
	}
	
	public Vector<Vector<String>> queryHistoryDetails(String obid) {
		Vector<Vector<String>> result = new Vector<Vector<String>>();
		Vector<String> server = null;
		
		String sqlStatement =
			"select obid, workItemName, submittedTime, comment," +
			  " route, instructions, role, ServerName, ServerURL from ItemHistory where obid='" +
			  obid + "'";
			//"select ServerName, ServerURL, userId, password from Servers ";
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(sqlStatement, null);
		
		for (int i = 0; i < cursor.getCount(); i ++)  {
			server = new Vector<String>();
			
			server.add(cursor.getString(0)); // obid
			server.add(cursor.getString(1)); // workItemName
			server.add(cursor.getString(2)); // submittedTime
			server.add(cursor.getString(3)); // comment
			server.add(cursor.getString(4)); // route
			server.add(cursor.getString(5)); // instructions
			server.add(cursor.getString(6)); // role
			server.add(cursor.getString(7)); // ServerName
			server.add(cursor.getString(8)); // ServerURL
			
			result.add(server);
			cursor.moveToNext();
		}
		cursor.close();
		return result;
	}
	
	public Vector<Vector<String>> queryServers() {
		Vector<Vector<String>> result = new Vector<Vector<String>>();
		Vector<String> server = null;
		
		String sqlStatement = "select ServerName, ServerURL, userId, password from Servers ";
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(sqlStatement, null);
		
		for (int i = 0; i < cursor.getCount(); i ++)  {
			server = new Vector<String>();
			
			server.add(cursor.getString(0)); // ServerName
			server.add(cursor.getString(1)); // ServerURL
			server.add(cursor.getString(2)); // userId
			server.add(cursor.getString(3)); // password
			
			result.add(server);
			cursor.moveToNext();
		}
		cursor.close();
		return result;
	}
	
	
	/*
	 * public initailization
	 */
	
	public static DatabaseProcessor initialize(Context context) {
		DatabaseProcessor result = null;
		
		result = new DatabaseProcessor(context);
		
		return result;
	
	}
	
	private DatabaseProcessor(Context context) {
		super(context, DB_NAME, null, DB_VERSION);
		// TODO Auto-generated constructor stub
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		db.execSQL(createTableScript);
		db.execSQL(createTableScript1);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
	}
	
	
	

}
